
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 02/26/2014 00:01:06
-- Generated from EDMX file: D:\git\rbsfpp\KKU.RBSFPP\KKU.RBSFPP.Shared\Shared\DAC\RBSFPPModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [KKU_RBSFPP_Database];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PrivilegeLevelAccount]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Members] DROP CONSTRAINT [FK_PrivilegeLevelAccount];
GO
IF OBJECT_ID(N'[dbo].[FK_CategoryProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_CategoryProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductUnitProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_ProductUnitProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductPricingPerUnitProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_ProductPricingPerUnitProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductProductTracking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductTrackings] DROP CONSTRAINT [FK_ProductProductTracking];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductPricingPerUnitProductTracking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductTrackings] DROP CONSTRAINT [FK_ProductPricingPerUnitProductTracking];
GO
IF OBJECT_ID(N'[dbo].[FK_MemberProductTracking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductTrackings] DROP CONSTRAINT [FK_MemberProductTracking];
GO
IF OBJECT_ID(N'[dbo].[FK_FactorFactorValue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactorValues] DROP CONSTRAINT [FK_FactorFactorValue];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductProductPricingPerUnitTracking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductPricingPerUnitTrackings] DROP CONSTRAINT [FK_ProductProductPricingPerUnitTracking];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Members]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Members];
GO
IF OBJECT_ID(N'[dbo].[PrivilegeLevels]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PrivilegeLevels];
GO
IF OBJECT_ID(N'[dbo].[Categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Categories];
GO
IF OBJECT_ID(N'[dbo].[ProductUnits]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductUnits];
GO
IF OBJECT_ID(N'[dbo].[ProductPricingPerUnits]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductPricingPerUnits];
GO
IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];
GO
IF OBJECT_ID(N'[dbo].[ProductTrackings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductTrackings];
GO
IF OBJECT_ID(N'[dbo].[Factors]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Factors];
GO
IF OBJECT_ID(N'[dbo].[FactorValues]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactorValues];
GO
IF OBJECT_ID(N'[dbo].[ProductPricingPerUnitTrackings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductPricingPerUnitTrackings];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Members'
CREATE TABLE [dbo].[Members] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Username] varchar(50)  NOT NULL,
    [PersonalId] varchar(13)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [Address] nvarchar(max)  NULL,
    [TelephoneNumber] nvarchar(50)  NULL,
    [PrivilegeLevelId] int  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'PrivilegeLevels'
CREATE TABLE [dbo].[PrivilegeLevels] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'ProductUnits'
CREATE TABLE [dbo].[ProductUnits] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'ProductPricingPerUnits'
CREATE TABLE [dbo].[ProductPricingPerUnits] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [PurchasingPrice] float  NOT NULL,
    [SellingPrice] float  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [CategoryId] int  NOT NULL,
    [ProductUnitId] int  NOT NULL,
    [ProductPricingPerUnitId] int  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'ProductTrackings'
CREATE TABLE [dbo].[ProductTrackings] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Action] varchar(1)  NOT NULL,
    [Unit] float  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL,
    [ProductId] int  NOT NULL,
    [ProductPricingPerUnitId] int  NOT NULL,
    [MemberId] int  NOT NULL
);
GO

-- Creating table 'Factors'
CREATE TABLE [dbo].[Factors] (
    [FactorId] int IDENTITY(1,1) NOT NULL,
    [FactorName] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL
);
GO

-- Creating table 'FactorValues'
CREATE TABLE [dbo].[FactorValues] (
    [FactorValueId] int IDENTITY(1,1) NOT NULL,
    [Value] float  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL,
    [FactorId] int  NOT NULL
);
GO

-- Creating table 'ProductPricingPerUnitTrackings'
CREATE TABLE [dbo].[ProductPricingPerUnitTrackings] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [PurchasingPrice] float  NOT NULL,
    [SellingPrice] float  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [DeleteOn] datetime  NULL,
    [ProductId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Members'
ALTER TABLE [dbo].[Members]
ADD CONSTRAINT [PK_Members]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PrivilegeLevels'
ALTER TABLE [dbo].[PrivilegeLevels]
ADD CONSTRAINT [PK_PrivilegeLevels]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProductUnits'
ALTER TABLE [dbo].[ProductUnits]
ADD CONSTRAINT [PK_ProductUnits]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProductPricingPerUnits'
ALTER TABLE [dbo].[ProductPricingPerUnits]
ADD CONSTRAINT [PK_ProductPricingPerUnits]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProductTrackings'
ALTER TABLE [dbo].[ProductTrackings]
ADD CONSTRAINT [PK_ProductTrackings]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [FactorId] in table 'Factors'
ALTER TABLE [dbo].[Factors]
ADD CONSTRAINT [PK_Factors]
    PRIMARY KEY CLUSTERED ([FactorId] ASC);
GO

-- Creating primary key on [FactorValueId] in table 'FactorValues'
ALTER TABLE [dbo].[FactorValues]
ADD CONSTRAINT [PK_FactorValues]
    PRIMARY KEY CLUSTERED ([FactorValueId] ASC);
GO

-- Creating primary key on [Id] in table 'ProductPricingPerUnitTrackings'
ALTER TABLE [dbo].[ProductPricingPerUnitTrackings]
ADD CONSTRAINT [PK_ProductPricingPerUnitTrackings]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PrivilegeLevelId] in table 'Members'
ALTER TABLE [dbo].[Members]
ADD CONSTRAINT [FK_PrivilegeLevelAccount]
    FOREIGN KEY ([PrivilegeLevelId])
    REFERENCES [dbo].[PrivilegeLevels]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PrivilegeLevelAccount'
CREATE INDEX [IX_FK_PrivilegeLevelAccount]
ON [dbo].[Members]
    ([PrivilegeLevelId]);
GO

-- Creating foreign key on [CategoryId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_CategoryProduct]
    FOREIGN KEY ([CategoryId])
    REFERENCES [dbo].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryProduct'
CREATE INDEX [IX_FK_CategoryProduct]
ON [dbo].[Products]
    ([CategoryId]);
GO

-- Creating foreign key on [ProductUnitId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_ProductUnitProduct]
    FOREIGN KEY ([ProductUnitId])
    REFERENCES [dbo].[ProductUnits]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductUnitProduct'
CREATE INDEX [IX_FK_ProductUnitProduct]
ON [dbo].[Products]
    ([ProductUnitId]);
GO

-- Creating foreign key on [ProductPricingPerUnitId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_ProductPricingPerUnitProduct]
    FOREIGN KEY ([ProductPricingPerUnitId])
    REFERENCES [dbo].[ProductPricingPerUnits]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductPricingPerUnitProduct'
CREATE INDEX [IX_FK_ProductPricingPerUnitProduct]
ON [dbo].[Products]
    ([ProductPricingPerUnitId]);
GO

-- Creating foreign key on [ProductId] in table 'ProductTrackings'
ALTER TABLE [dbo].[ProductTrackings]
ADD CONSTRAINT [FK_ProductProductTracking]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProductTracking'
CREATE INDEX [IX_FK_ProductProductTracking]
ON [dbo].[ProductTrackings]
    ([ProductId]);
GO

-- Creating foreign key on [ProductPricingPerUnitId] in table 'ProductTrackings'
ALTER TABLE [dbo].[ProductTrackings]
ADD CONSTRAINT [FK_ProductPricingPerUnitProductTracking]
    FOREIGN KEY ([ProductPricingPerUnitId])
    REFERENCES [dbo].[ProductPricingPerUnits]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductPricingPerUnitProductTracking'
CREATE INDEX [IX_FK_ProductPricingPerUnitProductTracking]
ON [dbo].[ProductTrackings]
    ([ProductPricingPerUnitId]);
GO

-- Creating foreign key on [MemberId] in table 'ProductTrackings'
ALTER TABLE [dbo].[ProductTrackings]
ADD CONSTRAINT [FK_MemberProductTracking]
    FOREIGN KEY ([MemberId])
    REFERENCES [dbo].[Members]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MemberProductTracking'
CREATE INDEX [IX_FK_MemberProductTracking]
ON [dbo].[ProductTrackings]
    ([MemberId]);
GO

-- Creating foreign key on [FactorId] in table 'FactorValues'
ALTER TABLE [dbo].[FactorValues]
ADD CONSTRAINT [FK_FactorFactorValue]
    FOREIGN KEY ([FactorId])
    REFERENCES [dbo].[Factors]
        ([FactorId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FactorFactorValue'
CREATE INDEX [IX_FK_FactorFactorValue]
ON [dbo].[FactorValues]
    ([FactorId]);
GO

-- Creating foreign key on [ProductId] in table 'ProductPricingPerUnitTrackings'
ALTER TABLE [dbo].[ProductPricingPerUnitTrackings]
ADD CONSTRAINT [FK_ProductProductPricingPerUnitTracking]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProductPricingPerUnitTracking'
CREATE INDEX [IX_FK_ProductProductPricingPerUnitTracking]
ON [dbo].[ProductPricingPerUnitTrackings]
    ([ProductId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------